package wfmanage.action;

import java.sql.Date;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;

import com.util.DB;

import edu.wfmanage.models.Ticket;

import com.opensymphony.xwork2.ActionSupport;

public class ViewTickets extends ActionSupport{

	private int id;
	private String summary = "";
	private String category = "";
	private String device = "";
	private String os = "";
	private Date date;
	private String priority = "";
	private String cust_name = "";
	private String email = "";
	private String dept = "";
	private String office = "";
	private String phone = "";
	private String desc = "";
	private String comments = "";
	private ArrayList<Ticket> tickets;
	private ArrayList<String> names;
	private String name;
	private String support_level;
	private String status;
	
	private String commandbutton = "";
		
	public String execute() {
		
		names = ViewTickets.findStrings();
		support_level = ViewTickets.findSupportLevel();
		tickets = Ticket.find("where t.status_id=1");
		status = ViewTickets.findStatus();
				
		if(this.commandbutton.startsWith("Sign")){
			return "sign_out";
		}
		
		return "initial_entry";
		
	}
	
	public static String findStatus(){
		
		String status = null;
		ResultSet result = null;
		String query = "select status_name "
				 		+ "from status "
				 		+ "where status_id = 1;";
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		try {
		 while(result.next()){
			status = result.getString("status_name");
		 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return status;		
	}
	
        public static ArrayList<String> findSupportExecutiveLevel(){
		
		ArrayList<String> supports = new ArrayList<String>();
		String support_level = null;
		ResultSet result = null;
		String query = "select role_name from role r, support_priority s, priority p, ticket t " + 
		  " where r.role_id = s.support_id and s.priority_id=p.priority_id and p.priority_name=t.priority and "
				+ "t.status_id= 1 order by t.ticket_id ";
		
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		
		try {
			while(result.next()){
				support_level = result.getString("role_name");
				supports.add(support_level);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return supports;
	}
	
	
public static String findSupportLevelPriority(String priority){
		
		String support_level = null;
		ResultSet result = null;
		String query = "select distinct role.role_name as role_name"
				        + "from role, support_priority, priority, ticket "
						+ "where priority.priority_name ='"+ priority + "' AND "
				        + "priority.priority_id = support_priority.priority_id AND "
						+ "support_priority.support_id = role.role_id;";
	
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		
		try {
			while(result.next()){
				support_level = result.getString("role_name");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return support_level;
	}
	
	public static String findSupportLevel(){
		
		String support_level = null;
		ResultSet result = null;
		String query = "select distinct role_name "
				        + "from role, support_priority, priority, ticket "
						+ "where ticket.priority = priority.priority_name AND "
				        + "priority.priority_id = support_priority.priority_id AND "
						+ "support_priority.support_id = role.role_id;";
	
		
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		
		try {
			while(result.next()){
				support_level = result.getString("role_name");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return support_level;
	}
	
	public static ArrayList<String> findNamesforRole(String support_name){
		
		ArrayList<String> usernames = new ArrayList<String>();
		ResultSet result = null;
		String name = "";
		
		String query = "select user_name from user, role, user_role " 
				+ "where user.user_id=user_role.user_id and user_role.role_id=role.role_id "
				+ "and role.role_name = '" + support_name + "'";
		
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		
		try {
			while(result.next()){
				name = result.getString("user_name");
				usernames.add(name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return usernames;
	}

	public static ArrayList<String> findStrings(){
		
		ArrayList<String> usernames = new ArrayList<String>();
		ResultSet result = null;
		String query = "select distinct user.user_name as support_name " 
						+ "from user, user_role, support_priority, priority, ticket "
						+ "where ticket.priority = priority.priority_name AND "
						+ "priority.priority_id = support_priority.priority_id AND "
						+ "support_priority.support_id = user_role.role_id AND "
						+ "user_role.user_id = user.user_id;";
		Connection con = DB.getConnection();
		result = DB.readFromDB(query, con);
		
		try {
			while(result.next()){
				String name = result.getString("support_name");
				usernames.add(name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return usernames;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getSummary() {
		return summary;
	}

	public void setSummary(String summary) {
		this.summary = summary;
	}

	public String getCategory() {
		return category;
	}

	public void setCategory(String category) {
		this.category = category;
	}

	public String getDevice() {
		return device;
	}

	public void setDevice(String device) {
		this.device = device;
	}

	public String getOs() {
		return os;
	}

	public void setOs(String os) {
		this.os = os;
	}

	public Date getDate() {
		return date;
	}

	public void setDate(Date date) {
		this.date = date;
	}

	public String getPriority() {
		return priority;
	}

	public void setPriority(String priority) {
		this.priority = priority;
	}

	public String getCust_name() {
		return cust_name;
	}

	public void setCust_name(String cust_name) {
		this.cust_name = cust_name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getDept() {
		return dept;
	}

	public void setDept(String dept) {
		this.dept = dept;
	}

	public String getOffice() {
		return office;
	}

	public void setOffice(String office) {
		this.office = office;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getDesc() {
		return desc;
	}

	public void setDesc(String desc) {
		this.desc = desc;
	}

	public String getComments() {
		return comments;
	}

	public void setComments(String comments) {
		this.comments = comments;
	}

	public ArrayList<Ticket> getTickets() {
		return tickets;
	}

	public void setTickets(ArrayList<Ticket> tickets) {
		this.tickets = tickets;
	}

	public ArrayList<String> getNames() {
		return names;
	}

	public void setNames(ArrayList<String> names) {
		this.names = names;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSupport_level() {
		return support_level;
	}

	public void setSupport_level(String support_level) {
		this.support_level = support_level;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getCommandbutton() {
		return commandbutton;
	}

	public void setCommandbutton(String commandbutton) {
		this.commandbutton = commandbutton;
	}

		
}
